package com.smartcampusbackend.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.smartcampusbackend.model.Grade;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

@Mapper
public interface GradeMapper extends BaseMapper<Grade> {
    
    /**
     * 获取学生的总学分
     * @param studentId 学生ID
     * @return 总学分
     */
    @Select("SELECT COALESCE(SUM(c.credits), 0) as totalCredits " +
            "FROM grade g " +
            "JOIN course c ON g.course_id = c.id " +
            "WHERE g.student_id = #{studentId} AND g.final_grade >= 60")
    Double getTotalCredits(@Param("studentId") String studentId);
    
    /**
     * 获取学生的成绩统计信息
     * @param studentId 学生ID
     * @return 统计信息
     */
    @Select("WITH CourseMaxGrades AS (\n" +
            "    SELECT \n" +
            "        course_id,\n" +
            "        MAX(CASE WHEN semester LIKE '%补考%' AND final_grade >= 60 THEN 1\n" +
            "                 WHEN semester NOT LIKE '%补考%' THEN 0\n" +
            "                 ELSE -1 END) AS has_passed_makeup,\n" +
            "        MAX(CASE WHEN semester NOT LIKE '%补考%' THEN final_grade ELSE NULL END) AS original_grade,\n" +
            "        MAX(CASE WHEN semester LIKE '%补考%' THEN final_grade ELSE NULL END) AS makeup_grade\n" +
            "    FROM grade\n" +
            "    WHERE student_id = #{studentId}\n" +
            "    GROUP BY course_id\n" +
            ")\n" +
            "SELECT \n" +
            "    COUNT(DISTINCT g.course_id) AS totalCourses,\n" +
            "    SUM(CASE WHEN cmg.has_passed_makeup = 1 THEN 1\n" +
            "             WHEN cmg.has_passed_makeup = -1 AND cmg.original_grade >= 60 THEN 1\n" +
            "             WHEN cmg.has_passed_makeup = 0 AND cmg.original_grade >= 60 THEN 1\n" +
            "             ELSE 0 END) AS passedCourses,\n" +
            "    AVG(CASE WHEN cmg.has_passed_makeup = 1 THEN 60\n" +
            "             WHEN cmg.has_passed_makeup = -1 AND cmg.makeup_grade IS NOT NULL THEN cmg.makeup_grade\n" +
            "             ELSE cmg.original_grade END) AS averageGrade,\n" +
            "    SUM(CASE WHEN cmg.has_passed_makeup = 1 THEN c.credits\n" +
            "             WHEN cmg.has_passed_makeup = -1 AND cmg.original_grade >= 60 THEN c.credits\n" +
            "             WHEN cmg.has_passed_makeup = 0 AND cmg.original_grade >= 60 THEN c.credits\n" +
            "             ELSE 0 END) AS totalCredits\n" +
            "FROM grade g\n" +
            "JOIN CourseMaxGrades cmg ON g.course_id = cmg.course_id\n" +
            "LEFT JOIN course c ON g.course_id = c.id\n" +
            "WHERE g.student_id = #{studentId}\n" +
            "GROUP BY g.student_id")
    Map<String, Object> getGradeStatistics(@Param("studentId") String studentId);
    
    /**
     * 获取课程的学生成绩列表（包含学生信息）
     * @param courseId 课程ID
     * @return 学生成绩列表
     */
    @Select("SELECT g.*, u.username as student_number, u.real_name as student_name " +
            "FROM grade g " +
            "LEFT JOIN user u ON g.student_id = u.id " +
            "WHERE g.course_id = #{courseId}")
    List<Map<String, Object>> getGradesByCourseWithStudentInfo(@Param("courseId") String courseId);

    /**
     * 更新学生成绩统计信息
     * 这个方法不直接执行SQL，而是触发数据库中的存储过程或函数来更新统计信息
     * 实际上是一个空操作，用于标记需要更新统计信息的地方
     */
    @Update("/* 触发学生成绩统计信息更新 - 实际由应用层处理 */ " +
            "SELECT 1 FROM grade WHERE student_id = #{studentId} LIMIT 1")
    void updateStudentGradeStatistics(@Param("studentId") String studentId);
} 